<?php

require_once('../../connection/_dbconnect_.php');
//require_once('m_activity_log.php');


function getQShifts($day){
	
	$parentArray = array();
	
	$sql = "select us.iduser as user,
				   qs.idqueue_shift as qshift,
				   qs.description as description
			from queue_shift qs \n
			join schedule s on (qs.idqueue_shift = s.idqueue_shift) \n
			join weekday w on (s.idweekday = w.idweekday) \n
			join user_schedule us on (s.idschedule = us.idschedule) \n
			where w.name = '".$day."'\n
			order by qs.idqueue_shift ASC;";
	
	//echo $sql;exit;
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) 
		return false;
	else if (mysql_num_rows($result)!=0){
	
		// create date processing to get day's date and feed it to next sql query
		$today = new DateTime('NOW');
		$today->setTimeZone(new DateTimeZone("America/Costa_Rica"));
		$timestamp = strtotime($today->format('Y-m-d'));
		$date = date('Y-m-d', strtotime(strtolower($day).' this week', $timestamp));		
	
		$sql2 = "select qsr.requesting_user,
						qsr.current_qshift,
						q1.description as desc1,
						qsr.target_user,
						qsr.target_qshift,
						q2.description as desc2 
				 from qshift_change_request qsr
				 join queue_shift q1 on q1.idqueue_shift = qsr.current_qshift
				 join queue_shift q2 on q2.idqueue_shift = qsr.target_qshift
				 where qsr.day_requested= '".$date."' and request_status='Processed';";
		
		//echo $sql2;exit;
		
		$result2 = mysql_query($sql2) or die("Query: $sql2 \n\n".mysql_error());
		
		if (!$result2) 
			return false;
		else if (mysql_num_rows($result2)==0) { //if there aren't any change requests for today then move on
	
			while($obj = mysql_fetch_object($result)){
				$childArray = array();		
				$childArray[] = $obj->user;
				$childArray[] = $obj->qshift;
				$childArray[] = $obj->description;
				
				$parentArray[]= $childArray;
			}

			return $parentArray;
			
		} elseif (mysql_num_rows($result2)!=0) { //if exist change requests then additional processing before return
			
			$swapsToView = array();			
			
			while($obj2 = mysql_fetch_object($result2)){						
				$swapsToView[] = $obj2->requesting_user;
				$swapsToView[] = $obj2->target_qshift;
				$swapsToView[] = $obj2->desc2;
				$swapsToView[] = $obj2->target_user;
				$swapsToView[] = $obj2->current_qshift ;
				$swapsToView[] = $obj2->desc1;				
			}
			
			
			while($obj = mysql_fetch_object($result)){
				$childArray = array();		
				$childArray[] = $obj->user;
				
				$swapExists= array_search($obj->user, $swapsToView);
				
				if($swapsToView[$swapExists] == $obj->user){
					$childArray[] = $swapsToView[$swapExists+1];
					$childArray[] = $swapsToView[$swapExists+2];					
				} else {
					$childArray[] = $obj->qshift;
					$childArray[] = $obj->description;
				}
				
				$parentArray[]= $childArray;
				
			}
			
			foreach($parentArray as $key => $row){
				
				$qshiftSlot[$key]= $row[1];
				$nameSlot[$key]= $row[0];
			}
			
			array_multisort($qshiftSlot, SORT_ASC, $nameSlot, SORT_ASC, $parentArray);
			
			//print_r($parentArray);exit;
			return $parentArray;
		}
	
	} else {
		echo '<h2>No data to show for today: '.$day."</h2>";
		exit;
	}
}

function getQShiftsUser($username){
	
	$parentArray = array();
	
	$sql = "select us.iduser as user,
				   u.fullname as name,
				   w.name as weekday,
				   qs.idqueue_shift as qshift,
				   qs.description as description
			from queue_shift qs \n
			join schedule s on (qs.idqueue_shift = s.idqueue_shift) \n
			join weekday w on (s.idweekday = w.idweekday) \n
			join user_schedule us on (s.idschedule = us.idschedule) \n
			join user u on (u.iduser = us.iduser) \n
			where u.iduser = '".$username."'\n
			order by w.idweekday ASC;";
	
	//echo $sql;exit;
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) 
		return false;
	else if (mysql_num_rows($result)!=0){
		while($obj = mysql_fetch_object($result)){
			$childArray = array();		
			$childArray[] = $obj->user;
			$childArray[] = $obj->name;
			$childArray[] = $obj->weekday;
			$childArray[] = $obj->qshift;
			$childArray[] = $obj->description;
			
			$parentArray[]= $childArray;
		}

		return $parentArray;
	} else {
		echo '<h2>No Q-Shift data to show for user: '.$username;
		exit;
	}
}


function getQShiftsUserWeek($username){
	
	$parentArray = array();
	
	$sql = "select us.iduser as user,
				   u.fullname as name,
				   w.name as weekday,
				   qs.idqueue_shift as qshift,
				   qs.description as description
			from queue_shift qs \n
			join schedule s on (qs.idqueue_shift = s.idqueue_shift) \n
			join weekday w on (s.idweekday = w.idweekday) \n
			join user_schedule us on (s.idschedule = us.idschedule) \n
			join user u on (u.iduser = us.iduser) \n
			where u.iduser = '".$username."'\n
			order by w.idweekday ASC;";
	
	//echo $sql;exit;
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) 
		return false;
	else if (mysql_num_rows($result)!=0){
		
		/* date processing
		 * get today's date and check for user's schedules for those days (if
		 * any) in the qshift change request table
		 */		
		$today = new DateTime('NOW');
		$today->setTimeZone(new DateTimeZone("America/Costa_Rica"));
		$timestamp = strtotime($today->format('Y-m-d'));
		$date = date('Y-m-d', strtotime('monday this week', $timestamp));
		$dateArray = explode('-', $date);		
		
		$sql2= "select  qsr.day_requested as date,
						qsr.requesting_user,
						qsr.current_qshift,
						q1.description as desc1,
						qsr.target_user,
						qsr.target_qshift,
						q2.description as desc2 
				 from qshift_change_request qsr
				 join queue_shift q1 on q1.idqueue_shift = qsr.current_qshift
				 join queue_shift q2 on q2.idqueue_shift = qsr.target_qshift
				 where qsr.day_requested between '".$dateArray[0]."-".$dateArray[1]."-".$dateArray[2]."' 
											 and '".$dateArray[0]."-".$dateArray[1] ."-".($dateArray[2] + 4)."'  
											 and request_status='Processed'
											 and (qsr.requesting_user = '".$username."' or qsr.target_user = '".$username."')
				 order by qsr.day_requested ASC;";
		
		//echo $sql2; exit;		
		
		$result2 = mysql_query($sql2) or die("Query: $sql2 \n\n".mysql_error());
		
		if (!$result2) 
			return false;
		else if (mysql_num_rows($result2)==0) { //if there aren't any change requests for today then move on
			
			while($obj = mysql_fetch_object($result)){
				$childArray = array();		
				$childArray[] = $obj->user;
				$childArray[] = $obj->name;
				$childArray[] = $obj->weekday;
				$childArray[] = $obj->qshift;
				$childArray[] = $obj->description;
				$childArray[] = "NO SWAP";
				
				$parentArray[]= $childArray;
			}

			return $parentArray;
			
		} elseif (mysql_num_rows($result2)!=0) { //if exist change requests then additional processing before return
			
			$swapsActive = array();	
			$weekArray = array('Monday','Tuesday','Wednesday','Thursday','Friday');
			
			// save all active swaps prefixed by the weekday
			while($obj2 = mysql_fetch_object($result2)){			
								
				if ($obj2->date == $dateArray[0].'-'.$dateArray[1].'-'.$dateArray[2]) { //add weekday depending on date
					$swapsActive[] = $weekArray[0];
				} elseif ($obj2->date == $dateArray[0].'-'.$dateArray[1].'-'.($dateArray[2]+1)){ 
					$swapsActive[] = $weekArray[1];
				} elseif ($obj2->date == $dateArray[0].'-'.$dateArray[1].'-'.($dateArray[2]+2)){ 
					$swapsActive[] = $weekArray[2];
				} elseif ($obj2->date == $dateArray[0].'-'.$dateArray[1].'-'.($dateArray[2]+3)){ 
					$swapsActive[] = $weekArray[3];
				} elseif ($obj2->date == $dateArray[0].'-'.$dateArray[1].'-'.($dateArray[2]+4)){ 
					$swapsActive[] = $weekArray[4];
				}
				$swapsActive[] = $obj2->requesting_user;
				$swapsActive[] = $obj2->target_qshift;
				$swapsActive[] = $obj2->desc2;
				$swapsActive[] = $obj2->target_user;
				$swapsActive[] = $obj2->current_qshift ;
				$swapsActive[] = $obj2->desc1;	

			}
			
			while($obj = mysql_fetch_object($result)){
				$childArray = array();		
				$childArray[] = $obj->user;
				$childArray[] = $obj->name;
				$childArray[] = $obj->weekday;
				$activeSWAP = "NOT SWAP";
				
				//check if there's a swap for this user
				$swapExists= array_search($obj->user, $swapsActive);
				
				if ($swapsActive[$swapExists] == $obj->user){
					if($swapsActive[$swapExists-1] == $obj->weekday) {
						$childArray[] = $swapsActive[$swapExists+1];
						$childArray[] = $swapsActive[$swapExists+2];	
						$swapsActive[$swapExists] = "used";
						$activeSWAP = "SWAP";
					} elseif ($swapExists>3) {			
						if($swapsActive[$swapExists-4] == $obj->weekday) {
							$childArray[] = $swapsActive[$swapExists+1];
							$childArray[] = $swapsActive[$swapExists+2];	
							$swapsActive[$swapExists] = "used";
							$activeSWAP = "SWAP";
						} else {				
							$childArray[] = $obj->qshift;
							$childArray[] = $obj->description;
						}
					} else {				
						$childArray[] = $obj->qshift;
						$childArray[] = $obj->description;
					}
				} else {				
					$childArray[] = $obj->qshift;
					$childArray[] = $obj->description;
				}
				
				$childArray[] = $activeSWAP;
				
				$parentArray[]= $childArray;				

			}

			//print_r($parentArray);exit;
			
			return $parentArray;			
		}
	} else {
		echo '<h2>No Q-Shift data to show for user: '.$username;
		exit;
	}
}

function getSwapData($requestor,$target){
	
	$parentArray = array();
	
	$sql = "SELECT x.weekday,
				   x.username as username_a,
				   x.qshift as qshift_a,
				   x.description as description_a,
				   y.username as username_b,
				   y.qshift as qshift_b,
				   y.description as description_b
			FROM `vw_user_week_qshifts` x
			LEFT JOIN `vw_user_week_qshifts` y ON ( x.weekday = y.weekday )
			left join weekday w on x.weekday = w.name
			WHERE x.username = '".$requestor."'
			AND y.username = '".$target."'
			ORDER BY w.idweekday ASC;";
	
	//echo $sql;exit;
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) 
		return false;
	else if (mysql_num_rows($result)!=0){
	
		/* date processing
		 * get today's date and check for user's schedules for those days (if
		 * any) in the qshift change request table
		 */
		$today = new DateTime('NOW');
		$today->setTimeZone(new DateTimeZone("America/Costa_Rica"));
		$timestamp = strtotime($today->format('Y-m-d'));
		$date = date('Y-m-d', strtotime('monday this week', $timestamp));
		$dateArray = explode('-', $date);
		
		$sql2= "select  qsr.day_requested as date,
						qsr.requesting_user,
						qsr.current_qshift,
						q1.description as desc1,
						qsr.target_user,
						qsr.target_qshift,
						q2.description as desc2 
				 from qshift_change_request qsr
				 join queue_shift q1 on q1.idqueue_shift = qsr.current_qshift
				 join queue_shift q2 on q2.idqueue_shift = qsr.target_qshift
				 where qsr.day_requested between '".$dateArray[0]."-".$dateArray[1]."-".$dateArray[2]."' 
											 and '".$dateArray[0]."-".$dateArray[1] ."-".($dateArray[2] + 4)."'  
											 and request_status='Processed'
				 order by qsr.day_requested ASC;";
		
		//echo $sql2; exit;

		$result2 = mysql_query($sql2) or die("Query: $sql2 \n\n".mysql_error());
		
		if (!$result2) 
			return false;
		else if (mysql_num_rows($result2)==0) { //if there aren't any change requests for today then move on
	
			while($obj = mysql_fetch_object($result)){
				$childArray = array();		
				$childArray[] = $obj->weekday;
				$childArray[] = $obj->username_a;
				$childArray[] = $obj->qshift_a;
				$childArray[] = $obj->description_a;
				$childArray[] = $obj->username_b;
				$childArray[] = $obj->qshift_b;
				$childArray[] = $obj->description_b;
				$childArray[] = "NOT SWAP";
				
				$parentArray[]= $childArray;
			}

			return $parentArray;
			
		} elseif (mysql_num_rows($result2)!=0) { //if exist change requests then additional processing before return
		
			$swapsActive = array();	
			$weekArray = array('Monday','Tuesday','Wednesday','Thursday','Friday');
			
			// save all active swaps prefixed by the weekday
			while($obj2 = mysql_fetch_object($result2)){			
								
				if ($obj2->date == $dateArray[0].'-'.$dateArray[1].'-'.$dateArray[2]) { //add weekday depending on date
					$swapsActive[] = $weekArray[0];
				} elseif ($obj2->date == $dateArray[0].'-'.$dateArray[1].'-'.($dateArray[2]+1)){ 
					$swapsActive[] = $weekArray[1];
				} elseif ($obj2->date == $dateArray[0].'-'.$dateArray[1].'-'.($dateArray[2]+2)){ 
					$swapsActive[] = $weekArray[2];
				} elseif ($obj2->date == $dateArray[0].'-'.$dateArray[1].'-'.($dateArray[2]+3)){ 
					$swapsActive[] = $weekArray[3];
				} elseif ($obj2->date == $dateArray[0].'-'.$dateArray[1].'-'.($dateArray[2]+4)){ 
					$swapsActive[] = $weekArray[4];
				}
				$swapsActive[] = $obj2->requesting_user;
				$swapsActive[] = $obj2->target_qshift;
				$swapsActive[] = $obj2->desc2;
				$swapsActive[] = $obj2->target_user;
				$swapsActive[] = $obj2->current_qshift ;
				$swapsActive[] = $obj2->desc1;	

			}			

			//print_r($swapsActive);exit;
			
			while($obj = mysql_fetch_object($result)){
				$childArray = array();		
				$childArray[] = $obj->weekday;
				$isSWAP = "NOT SWAP";
				
				//check if there's a swap for this user
				$swapExists= array_search($obj->username_a, $swapsActive);
				
				if ($swapsActive[$swapExists] == $obj->username_a){ 
					if($swapsActive[$swapExists-1] == $obj->weekday) { 
						$childArray[] = $swapsActive[$swapExists];
						$childArray[] = $swapsActive[$swapExists+1];
						$childArray[] = $swapsActive[$swapExists+2];	
						$swapsActive[$swapExists] = "used";
						$isSWAP = "SWAP";
					} elseif ($swapExists>3) {
						if($swapsActive[$swapExists-4] == $obj->weekday) {		
							$childArray[] = $swapsActive[$swapExists];
							$childArray[] = $swapsActive[$swapExists+1];
							$childArray[] = $swapsActive[$swapExists+2];
							$swapsActive[$swapExists] = "used";
							$isSWAP = "SWAP";
						} else {
							$childArray[] = $obj->username_a;
							$childArray[] = $obj->qshift_a;
							$childArray[] = $obj->description_a;
						}
					} else {
						$childArray[] = $obj->username_a;
						$childArray[] = $obj->qshift_a;
						$childArray[] = $obj->description_a;
					}
				} else {
					$childArray[] = $obj->username_a;
					$childArray[] = $obj->qshift_a;
					$childArray[] = $obj->description_a;
				}
				
				//check for swaps with user b
				$swapExists= array_search($obj->username_b, $swapsActive);
				
				if ($swapsActive[$swapExists] == $obj->username_b){
					if($swapsActive[$swapExists-1] == $obj->weekday) {
						$childArray[] = $swapsActive[$swapExists];
						$childArray[] = $swapsActive[$swapExists+1];
						$childArray[] = $swapsActive[$swapExists+2];
						$swapsActive[$swapExists] = "used";
						$isSWAP = "SWAP";
					} elseif ($swapExists>3) {
						if($swapsActive[$swapExists-4] == $obj->weekday) {			
							$childArray[] = $swapsActive[$swapExists];
							$childArray[] = $swapsActive[$swapExists+1];
							$childArray[] = $swapsActive[$swapExists+2];
							$swapsActive[$swapExists] = "used";
							$isSWAP = "SWAP";
						} else {
							$childArray[] = $obj->username_b;
							$childArray[] = $obj->qshift_b;
							$childArray[] = $obj->description_b;
						}
					} else {
						$childArray[] = $obj->username_b;
						$childArray[] = $obj->qshift_b;
						$childArray[] = $obj->description_b;						
					}
				} else {
					$childArray[] = $obj->username_b;
					$childArray[] = $obj->qshift_b;
					$childArray[] = $obj->description_b;
				}				
				$childArray[] = $isSWAP;
				
				$parentArray[]= $childArray;
			}			
			
			//print_r($parentArray);exit;
			
			return $parentArray;
		}
		
	} else {
		echo '<h2>No Q-Shift data to show for user: '.$target;
		exit;
	}
}

function getUsers(){
	$parentArray = array();

	$sql = "select u.iduser
			from user u\n
			join role r on (u.idrole = r.idrole)\n
			order by r.idrole ASC, u.iduser ASC";
	
	//echo $sql;exit;
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	while($obj = mysql_fetch_object($result)){
		$parentArray[] = $obj->iduser;
	}	
	return $parentArray;
}

function doSwap($weekday,$requestor,$requestorQShift,$target,$targetQShift){
	
	$today = new DateTime('NOW');
	$today->setTimeZone(new DateTimeZone("America/Costa_Rica"));
	$timestamp = strtotime($today->format('Y-m-d'));
	$date = date('Y-m-d', strtotime(strtolower($weekday).' this week', $timestamp));	
	
	if($date < $today->format('Y-m-d')) {
		echo "<h1> ERROR: You cannot SWAP for a day earlier than today this week!
			  <br>
			  <br>
			  Remember you can only SWAP for a day within the current week.</h2>";
		exit;
	}
	
	//check if calendar day exists and add it if not
	$sql = "select * from calendar_day where idcalendar_day= '".$date."';";
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) {
		return false;
		exit;
	}
	else if (mysql_num_rows($result)==0){
		
		$sql = "insert into calendar_day values ('".$date."');";
		
		$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
		
		if (!$result) 
			return false;	
	}
	
	$sql = "INSERT INTO qshift_change_request (`idqshift_change_request`, 
											   `requesting_user`, 
											   `current_qshift`, 
											   `target_user`, 
											   `target_qshift`, 
											   `day_requested`, 
											   `request_status`) 
			VALUES (NULL, 
					'".$requestor."', 
					'".$requestorQShift."', 
					'".$target."', 
					'".$targetQShift."', 
					'".$date."', 
					'Processed');";
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) {
		return false;
	} else {
	
		//$result=addLogEntry($date->format('Y-m-d'), "Schedule= ".$idschedule." was modified");	
		return true;
	}
}


?>